OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Modifying your insert trigger

Progress Version 9.1D modified the insert trigger that it places on a table in the foreign database when the Progress RECID support is selected. The modification reduces the level of contention between multiple users performing inserts simultaneously. The reduction in contention can produce performance improvement.

If your database was created using the Progress DB to MS SQL Server utility and Progress RECID support is enabled, Progress Software Corporation recommends that you re-create your database by dumping your data, executing the Progress DB to MS SQL Server migration again, and then reloading your data. If this is not possible, then you can manually change your existing MS SQL Server database by doing the following on each table that has been defined with Progress RECID support from your MS SQL Server Migration:

Drop the unique index created over the progress_recid field. There should be one index for each table created during the initial migration. The syntax is:

Syntax
DROP index <`database`>.<`owner`>.<`tablename`>#_#progress_recid.

Drop the old insert trigger. The name typically looks like _TI_<tablename>. The syntax is:

Syntax
DROP trigger <`database`>.<owner._TI_<`tablename`>

Add the new trigger. The syntax is:

CREATE TRIGGER _TI_<tablename> ON <tablename> FOR INSERT AS IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL BEGIN UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL FROM <tablename> t JOIN INSERTED i ON t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ SELECT CONVERT (int,@@identity) END

CREATE TRIGGER _TI_<tablename> ON <tablename> FOR INSERT AS 
    IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL 
    BEGIN 
       UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL 
          FROM <tablename> t JOIN INSERTED i ON 
          t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ 
          SELECT CONVERT (int,@@identity) 
    END

Sample code for the db.owner.Customer table is:

CREATE TRIGGER db.owner._TI_customer ON customer FOR INSERT AS IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL BEGIN UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL FROM customer t JOIN INSERTED i ON t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ SELECT CONVERT (int, @@identity) END

CREATE TRIGGER db.owner._TI_customer ON customer FOR INSERT AS 
   IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL 
   BEGIN 
      UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL 
      FROM customer t JOIN INSERTED i ON 
      t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ 
      SELECT CONVERT (int, @@identity) 
   END

Create a new nonunique index for each table naming the new index the same name as was dropped earlier in Step 1. Make sure it is not unique. The syntax is:


CREATE INDEX <`database`>.<`owner`>.<`table`>#_#progress_recid ON <`tablename`>(PROGRESS_RECID)

A second index must be created for the progress_recid_ident field. This index must be created as unique and named <tablename>#_#progress_recid_ident. The syntax is:


CREATE UNIQUE INDEX <`tablename`>#_#progress_recid_ident_ ON <`tablename`>(PROGRESS_RECID_IDENT_

An example of the two indices that must be created for the customer table is as follows:


CREATE INDEX customer#_#progress_recid ON CUSTOMER (PROGRESS_RECID) CREATE UNIQUE INDEX customer#_#progress_recid_ident_ ON CUSTOMER(PROGRESS_RECID_IDENT_)